import { QueryTypes } from "sequelize";
import { sequelize } from "../lib/database.js";
import logger from "../lib/log4js.js";
import { RuFormMapper } from "./ru-form.ts";
import { USER_TYPES } from "../entity/common-defines.ts";
import { XlsxData } from "./xlsx-data.ts";
import { EvaProjectMapper } from "./eva-project.ts";
import { roundNumber } from "../lib/util.js";
class ResultNewCadreMapper extends XlsxData {

  private formMapper: RuFormMapper = new RuFormMapper;
  private projectMapper: EvaProjectMapper = new EvaProjectMapper;
  public async getResultData(project_id: string, test: boolean): Promise<SheetData> {

    const form: any = await this.formMapper.findOneByQuery({ where: { user_type: USER_TYPES.NEW_CADRE } })
    const formId: string = form?.id;
    if (!formId) throw new Error("对应测评表不存在")

    const sql = `
    select 
  tl.user_name,
  tl.a/total *100 a ,
  tl.b/total *100 b, 
  tl.c/total *100 c,
  tl.d/total *100 d,
  (tl.a+tl.b)/total *100  posiv_pert
from (               
      select        
        user_name,
        rule_name,
        round(ifnull(max(CASE WHEN vote_value = '基本认同' THEN ifnull(total,0) end),0),2)  AS a,
        round(ifnull(max(CASE WHEN vote_value = '认同' THEN ifnull(total,0) end),0),2)  AS b,
        round(ifnull(max(CASE WHEN vote_value = '不认同' THEN ifnull(total,0) end),0),2)  AS c,
        round(ifnull(max(CASE WHEN vote_value = '不了解' THEN ifnull(total,0) end),0),2)  AS d,
        sum(total) total,
        round(100*(ifnull(max(CASE WHEN vote_value = '基本认同' THEN ifnull(total,0) end),0)+ifnull(max(CASE WHEN vote_value = '认同' THEN ifnull(total,0) end),0))/sum(total) ,2) posiv_pert 
      from (  
        select
			rfr.form_id,
			ea.rule_name,
			ea.user_name,
			json_value(ifnull(ea.rule_value, '[0]'), '$[0]') vote_value,		
			count(*) total
		from
			eva_answer ea
		inner join eva_project_owner epo on
			epo.project_id = ea.project_id
			and epo.id = ea.project_owner_id
		inner join owner_name on2 on
			on2.id = epo.owner_name_id    
		inner join ru_form_rule rfr on
			rfr.form_id = ea.rule_form_id
			and rfr.second_id = ea.rule_id    
		where
      ea.is_test is ?
			and ea.project_id = ?
			and ea.user_type = ?
			and ea.rule_form_id = ?
			and ea.rule_name='对提拔该中层领导人员的看法'
	    group by
			ea.user_id,vote_value
) nl 
group by  nl.user_name
) tl	
        `;
    const sql2 = `select 
  group_concat(DISTINCT json_value(ifnull(ea.rule_value, '[0]'), '$[0]'),',') content
from eva_answer ea where ea.is_test is ? and  ea.project_id=? and  ea.rule_name ='您对加强和改进本单位选人用人工作有何意见和建议?'`
    try {
      const result = await sequelize.query(sql, {
        replacements: [test, project_id, USER_TYPES.NEW_CADRE, formId],
        type: QueryTypes.SELECT
      })
      const result2 = await sequelize.query(sql2, {
        replacements: [test, project_id],
        type: QueryTypes.SELECT
      })
      if (result && result.length > 0) {
        const project: any = await this.projectMapper.findOne({ pk: project_id });
        return {
          title: `${project.object_group_name}-${project.ef_year}年${form.name}`,
          name: form.name, data: result, data2: result2
        };
      }
    } catch (error) {
      logger.error(error);
    }
    return { data: [] };
  }

  async exportXlsxSheet(test: boolean, project_id: string, rowStart: number, index: number): Promise<SheetMeta> {
    const { title, name, data, data2 } = await this.getResultData(project_id, test);
    if (data.length === 0) {
      return { merges: [], data: [], name };
    }
    const result_data = [];
    result_data.push([`${index + 1}.${title}`, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""])
    const header = ["一、新提拔中层领导人员民主评议结果", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""]
    result_data.push(header);
    const top_names = ["评价对象", "", "", "认同", "", "", "基本认同", "", "", "不认同", "", "", "不了解", "", "", "认同和基本认同所占百分比", "", ""];
    result_data.push(top_names);

    let ms = rowStart + 3;
    const data_merges = [];
    for (const { user_name, a, b, c, d, posiv_pert } of data) {
      result_data.push([user_name, "", "", roundNumber(a), "", "", roundNumber(b), "", "", roundNumber(c), "", "", roundNumber(d), "", "", roundNumber(posiv_pert), "", ""]);
      data_merges.push({ s: { c: 0, r: ms }, e: { c: 2, r: ms } });
      data_merges.push({ s: { c: 3, r: ms }, e: { c: 5, r: ms } });
      data_merges.push({ s: { c: 6, r: ms }, e: { c: 8, r: ms } });
      data_merges.push({ s: { c: 9, r: ms }, e: { c: 11, r: ms } });
      data_merges.push({ s: { c: 12, r: ms }, e: { c: 14, r: ms } });
      data_merges.push({ s: { c: 15, r: ms }, e: { c: 17, r: ms } });
      ms += 1;
    }
    result_data.push(["二、干部职工对本单位选人用人工作的意见建议", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""])


    if (data2 && data2.length === 1 && data2[0].content) {
      const cnts = data2[0].content.split(",");
      for (const cnt of cnts) {
        if (cnt) {
          result_data.push([cnt, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""])
          data_merges.push({ s: { c: 0, r: ms }, e: { c: 18, r: ms } });
          ms += 1;
        }
      }
    }
    const merges = [
      { s: { c: 0, r: rowStart }, e: { c: 18, r: rowStart } },

      { s: { c: 0, r: rowStart + 1 }, e: { c: 18, r: rowStart + 1 } },

      { s: { c: 0, r: rowStart + 2 }, e: { c: 2, r: rowStart + 2 } },
      { s: { c: 3, r: rowStart + 2 }, e: { c: 5, r: rowStart + 2 } },
      { s: { c: 6, r: rowStart + 2 }, e: { c: 8, r: rowStart + 2 } },
      { s: { c: 9, r: rowStart + 2 }, e: { c: 11, r: rowStart + 2 } },
      { s: { c: 12, r: rowStart + 2 }, e: { c: 14, r: rowStart + 2 } },
      { s: { c: 15, r: rowStart + 2 }, e: { c: 17, r: rowStart + 2 } },

      { s: { c: 0, r: ms }, e: { c: 18, r: ms } },

      ...data_merges

    ];
    return { merges, data: result_data, name };

  }
}

export { ResultNewCadreMapper }